﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using WF_Business;

namespace ExtBusiness.Inspect
{
  public  class Xnjc
    {
      public DataTable TaskQueryDetail_list(double dHourInEveryDay,string iid) 
      {
          string strSql = string.Format(@"select 编号,岗位,经办人,
        to_char(接件时间,'yyyy-mm-dd hh24:mi:ss') 接件时间,
        to_char(提交时间,'yyyy-mm-dd hh24:mi:ss') 提交时间,
        trunc((办理时间)/{1})||'天'||abs(trunc((mod((办理时间),{1})/3600)))||'小时',
        岗位编号,
        (select m.isback
          from st_work_item_hist m
         where m.iid = 编号
           and userid = t.userid and m.wiid=t.岗位编号) isback,
         (select trunc((s.exusedtime) / {1}) || '天' ||
               abs(trunc((mod((s.exusedtime), {1}) / 3600))) || '小时'
          from st_work_item_hist s
         where s.iid = '{0}'
           and  s.wiid=t.岗位编号)  已用时间,
           (select trunc((s.extotaltime) / {1}) || '天' ||
               abs(trunc((mod((s.extotaltime), {1}) / 3600))) || '小时'
          from st_work_item_hist s
         where s.iid = '{0}'
           and  s.wiid=t.岗位编号)  最大时限                  
         from sv_work_item_list t where 编号='{0}' Order By 岗位编号", iid, dHourInEveryDay);

          //获取数据
          System.Data.DataTable dtSource;
          SysParams.OAConnection().RunSql(strSql, out dtSource);
          return dtSource;
      }

      public DataTable Ajxx_list(string strUserNo, string strUserName, string userid, bool ISsystemUser)
      {
          System.Data.DataTable dtOut;
          //添加业务删除标识条件 and i.isdelete <>1  addby zhongjian 20091222
          string strSql = "select a.iid,a.name from st_instance a,st_workflow b where 1=1 and a.wid=b.wid and a.isdelete <>1";

          if (!string.IsNullOrEmpty(strUserNo))
          {
              strSql += (" and iid like '%" + strUserNo + "%'");
          }

          if (!string.IsNullOrEmpty(strUserName))
          {
              strSql += (" and name like '%" + strUserName + "%'");
          }

          if (!ISsystemUser)//不是系统管理员
          {
              strSql += @" and b.wname in 
                                (select t.task  from xt_query_right t  where t.userid = '" + userid + "')";
          }


          SysParams.OAConnection().RunSql(strSql, out dtOut);
          return dtOut;
      }

      public DataRow inspectquerydetial_list(string id)
      {
          //添加业务删除标识条件 and i.isdelete <>1  addby zhongjian 20091222
      string    strSql = "select (select user_name from st_user where userid=记载人) 记载人,(select user_name from st_user where userid=被告人) 被告人,(select user_name from st_user where userid=裁决人) 裁决人,";
          strSql += "(select w.wname from st_workflow w,st_instance i where i.wid=w.wid and i.iid=xt_inspect.iid and i.isdelete<>1) 流程名,";
          strSql += "(select w.step from st_work_item w where w.wiid=xt_inspect.wiid union select h.step from st_work_item_hist h where h.wiid=xt_inspect.wiid) 结点名,";
          strSql += "to_char(违规时间,'yyyy-mm-dd hh24:mi:ss') 违规时间,";
          strSql += "to_char(记载时间,'yyyy-mm-dd hh24:mi:ss') 记载时间,";
          strSql += "to_char(申诉时间,'yyyy-mm-dd hh24:mi:ss') 申诉时间,";
          strSql += "to_char(裁决时间,'yyyy-mm-dd hh24:mi:ss') 裁决时间,";
          strSql += "违规事实,事件类型,记载描述,申诉描述,裁决描述,是否成立,id,wiid  from xt_inspect where id=" + id;

          System.Data.DataTable dtOut;
          SysParams.OAConnection().RunSql(strSql, out dtOut);
          System.Data.DataRow rs= dtOut.Rows[0];
          return rs;
      }


      public DataTable insurgedetail_list(string id,string step){
        string  sql1 = string.Format(@"select (select user_name from st_user where userid=k.userid) username,
                               to_char(exbeginmonitor, 'YYYY-MM-DD HH:MM:SS'),
                               trunc(exusedtime / {2}) || '天' ||
                               trunc((mod(exusedtime, {2}) / 3600)) || '小时' ||
                               trunc((mod(exusedtime, 3600) /60)) || '分钟',
                               exusedtime,extotaltime
                          from sv_work_step k
                         where iid = '{0}' and step = '{1}' and rownum<2 order by wiid desc
                        ", id, step, SystemConfig.SecondInEveryDay);
          System.Data.DataTable rs1;
          SysParams.OAConnection().RunSql(sql1, out rs1);
          return rs1;
      }

      public string statview_sql1(string step,string type)
      {
          string sql = "SELECT timeout FROM ST_STEP WHERE SName='" + step + "' AND Wid in(SELECT WID FROM ST_WORKFLOW WHERE ROT=0 AND WNAME='" + type + "')";
          string cnys = SysParams.OAConnection().GetValue(sql);
          return cnys;
      }

      public DataTable statview_sql2(string d_s, string d_e, string step,  string type) 
      {
       string   sql = "select count(*),";
          sql += "trunc(sum(exusedtime)/count(*))";

          //添加业务删除标识条件 and i.isdelete <>1  addby zhongjian 20091222
          sql += "  from  sv_work_step ";
          sql += " where iid in (select distinct iid from st_instance where isdelete <>1 and wid in (select wid from st_workflow where wname = '" + type + "' and status=2)";

          if (d_s.Equals("") && d_e.Equals(""))
          {
              sql += ")";
          }
          else
          {
              sql += " and ExBeginMonitor>=to_date('" + d_s + "','YYYY-MM-DD') and ExBeginMonitor<=to_date('" + d_e + "','YYYY-MM-DD'))";
          }

          sql += " and step='" + step + "'";

          System.Data.DataTable rs1;
          SysParams.OAConnection().RunSql(sql, out rs1);
          return rs1;
      }

      public DataTable statview_sql3(string d_s, string d_e,string type)
      {
          System.Data.DataTable rs;
          string sql = string.Empty;
          //添加业务删除标识条件 and i.isdelete <>1  addby zhongjian 20091222
          sql = "select step from st_work_item_hist ";
          sql += " where iid in (select distinct iid from st_instance where isdelete <>1 and status=2 and rownum=1 and wid in (select wid from st_workflow where wname = '" + type + "')";

          if (d_s.Equals("") && d_e.Equals(""))
          {
              sql += ")";
          }
          else
          {
              sql += " and ExBeginMonitor>=to_date('" + d_s + "','YYYY-MM-DD') and ExBeginMonitor<=to_date('" + d_e + "','YYYY-MM-DD'))";
          }
          sql += " order by wiid ";

          SysParams.OAConnection().RunSql(sql, out rs);
          return rs;
      }

      /// <summary>
      /// 挂起信息查询
      /// </summary>
      /// <param name="sql"></param>
      /// <param name="userid"></param>
      /// <param name="flag"></param>
      /// <returns></returns>
      public DataTable suspend_query_list(string sql, string userid, string flag)
      {
          //添加业务删除标识条件 and i.isdelete <>1  addby zhongjian 20091222
          if (flag == "ygq") //已挂起
          {
              sql = @"select s.iid serial,
                   (select user_name from st_user where userid=s.userid and rownum<2) username,
                   s.start_date sqsj,
                   s.DESCRIPTION memo,
                   s.stpname,
                   SUSPEND_TYPE,
                   s.id,
                   b.wname
              from st_suspend s, st_instance a, st_workflow b
             where a.wid = b.wid
               and a.iid = s.iid
               and s.end_userid is null
               and s.end_date is null";
          }
          else if (flag == "nogq")//未挂起
          {
              sql = @"select s.serial,
                   s.suspend_type,
                   (select user_name from st_user where userid=s.userid and rownum<2) username,
                   apply_time sqsj,
                (select step from st_work_item where wiid=s.HANDLEID) stpname,
                   s.memo,
                   id,
                   b.wname
              from xt_suspend_apply s, st_instance a, st_workflow b
             where a.wid = b.wid
               and a.iid = s.serial
               and s.ischeckup is null
               and s.isdelete is null";
          }
          else//解除挂起
          {
              sql = @"select s.serial,
                   s.suspend_type,
                   (select user_name from st_user where userid=s.userid and rownum<2) username,
                    (select step from st_work_item where wiid=s.HANDLEID) stpname,
                   apply_time sqsj,
                   s.memo,
                   id,
                   b.wname
              from xt_suspend_apply s, st_instance a, st_workflow b
             where a.wid = b.wid
               and a.iid = s.serial
               and s.ischeckup = '1'
               and a.isdelete <> 1";
          }

          //***************************************************//

          if (flag != "ygq")//不是已挂起的案件
          {
              bool ISsystemUser = Admin.SystemManager.IsSystemUser(userid);
              if (!ISsystemUser)//非系统管理员
              {
                  sql += string.Format(" and s.nextno in (select distinct a.FLOW_NO from XT_SUSPEND_FLOW a,XT_SUSPENDFLOW_USERID b where a.ID=b.FLOW_ID and b.USERID='{0}') and a.wid in(select SERIAL_ID from XT_SUSPENDFLOW_USERID where userid='{0}')", userid);
              }
          }

          System.Data.DataTable dtOut;
          SysParams.OAConnection().RunSql(sql, out dtOut);
          return dtOut;
      }
    }
}
